package com.css.fxfzypg.modules.rating.library.dao;

import com.css.fxfzypg.modules.rating.library.model.entity.KpExamScoringDbIndex3;
import com.css.fxfzypg.modules.rating.library.model.vo.KpExamScoringDbIndex3WithDataVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

@Slf4j
@Repository
public class KpExamScoringDbIndex3Repository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public void batchSave(List<KpExamScoringDbIndex3> index3List) {
        String sql = "INSERT INTO kp_exam_scoring_db_index3(id, kpesdi2_id, kpi3d_id, score, index_standard, create_time) values(:id, :kpesdi2Id, :kpi3dId, :score, :indexStandard, :createTime)";
        namedParameterJdbcTemplate.batchUpdate(sql, SqlParameterSourceUtils.createBatch(index3List.toArray()));
    }

    public void updateById(KpExamScoringDbIndex3 kpExamScoringDbIndex3) {
        StringBuilder sql = new StringBuilder("update kp_exam_scoring_db_index3 set ");
        if (ObjectUtils.isNotEmpty(kpExamScoringDbIndex3.getKpi3dId())) {
            sql.append(" kpi3d_id = '").append(kpExamScoringDbIndex3.getKpi3dId()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpExamScoringDbIndex3.getScore())) {
            sql.append(" score = '").append(kpExamScoringDbIndex3.getScore()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(kpExamScoringDbIndex3.getScore())) {
            sql.append(" index_standard = '").append(kpExamScoringDbIndex3.getIndexStandard()).append("', ");
        }
        sql.deleteCharAt(sql.lastIndexOf(", "));
        sql.append(" where id = '").append(kpExamScoringDbIndex3.getId()).append("' ");
        jdbcTemplate.update(sql.toString());
    }

    public void insert(KpExamScoringDbIndex3 kpExamScoringDbIndex3) {
        String sql = "INSERT INTO kp_exam_scoring_db_index3(id, kpesdi2_id, kpi3d_id, score, index_standard, create_time) values(:id, :kpesdi2Id, :kpi3dId, :score, :indexStandard, :createTime)";
        namedParameterJdbcTemplate.update(sql, new BeanPropertySqlParameterSource(kpExamScoringDbIndex3));
    }

    public KpExamScoringDbIndex3 getById(String index3Id) {
        try {
            String sql = "select * from kp_exam_scoring_db_index3 where id = '" + index3Id + "' ";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(KpExamScoringDbIndex3.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public void deleteById(String id) {
        String sql = "delete from kp_exam_scoring_db_index3 where id = '" + id + "'";
        jdbcTemplate.update(sql);
    }

    public Integer countByKpesdi2Id(String kpesdi2Id) {
        String sql = "select count(*) from kp_exam_scoring_db_index3 where kpesdi2_id = '" + kpesdi2Id + "'";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    public List<KpExamScoringDbIndex3> list(KpExamScoringDbIndex3 index3Param) {
        StringBuilder sql = new StringBuilder("select * from kp_exam_scoring_db_index3 where 1=1");
        if (ObjectUtils.isNotEmpty(index3Param.getId())) {
            sql.append(" and id = '").append(index3Param.getId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(index3Param.getKpesdi2Id())) {
            sql.append(" and kpesdi2_id = '").append(index3Param.getKpesdi2Id()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(index3Param.getKpi3dId())) {
            sql.append(" and kpi3d_id = '").append(index3Param.getKpi3dId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(index3Param.getScore())) {
            sql.append(" and score = '").append(index3Param.getScore()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(index3Param.getIndexStandard())) {
            sql.append(" and index_standard = '").append(index3Param.getIndexStandard()).append("' ");
        }
        sql.append(" order by create_time asc");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(KpExamScoringDbIndex3.class));
    }

    public BigDecimal sumScoreByKesdi2Id(String index2Id) {
        return jdbcTemplate.queryForObject("select sum(score) from kp_exam_scoring_db_index3 where kpesdi2_id = '" + index2Id + "'", BigDecimal.class);
    }

    public BigDecimal sumScoreByKesdi1Id(String index1Id) {
        return jdbcTemplate.queryForObject("select sum(t1.score) from kp_exam_scoring_db_index3 t1 left join kp_exam_scoring_db_index2 t2 on t1.kpesdi2_id = t2.id where t2.kpesdi1_id = '" + index1Id + "'", BigDecimal.class);
    }

    public List<KpExamScoringDbIndex3WithDataVo> getByKesdId(String kesdId) {
        String sql = String.format("SELECT t3.*,t4.code from kp_exam_scoring_db_index1 t1 LEFT JOIN kp_exam_scoring_db_index2 t2 on t1.id=t2.kpesdi1_id LEFT JOIN kp_exam_scoring_db_index3 t3 on t2.id = t3.kpesdi2_id LEFT JOIN kp_index3_data t4 on t3.kpi3d_id = t4.id WHERE t1.kesd_id = '%s' and t4.is_system = 1", kesdId);
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(KpExamScoringDbIndex3WithDataVo.class));
    }
}
